package cn.hairui.blog.mapper;

import cn.hairui.blog.model.Artical;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import tk.mybatis.mapper.common.BaseMapper;

import java.util.List;
import java.util.Map;

/**
 * @author laoganbu
 * @date: 2019/3/4 16:47
 */
@Mapper
public interface ArticalMapper extends BaseMapper<Artical> {

    @Select("SELECT * FROM ARTICAL WHERE NAVID = #{navid} and SHOW_FLAG='Y' ORDER BY CREATEDATE DESC , ID DESC LIMIT #{count}")
    List<Artical> queryByNavid(Integer navid, int count);

    @Select("SELECT * FROM ARTICAL WHERE CATEGORYID = #{categoryId} and SHOW_FLAG='Y' ORDER BY CREATEDATE DESC LIMIT #{count}")
    List<Artical> findByCategoryIdLimited(Integer categoryId, int count);

    @Select("SELECT * FROM ARTICAL WHERE SHOW_FLAG='Y' ORDER BY LIKENUMS DESC LIMIT 0,#{count}")
    List<Artical> findHotLimited(int count);

    @Select("SELECT * FROM ARTICAL WHERE INTOP='on' AND SHOW_FLAG='Y'")
    List<Artical> findTop();

    @Select("SELECT * FROM ARTICAL")
    List<Artical> list();

    @Select("SELECT DISTINCT TAGS FROM ARTICAL WHERE TAGS IS NOT NULL AND tags != '' AND SHOW_FLAG='Y'")
    List<String> findAllTags();

    @Select("SELECT COUNT(1) FROM ARTICAL WHERE SHOW_FLAG='Y' AND TAGS LIKE CONCAT('%',#{tagName},'%')")
    int findCountBytag(String tagName);

    @Select("SELECT * FROM ARTICAL WHERE CATEGORYID=#{categoryId} AND SHOW_FLAG='Y' ORDER BY ID DESC")
    List<Artical> findByCategoryId(Integer categoryId);

    @Select("SELECT date_format(CREATEDATE, '%Y-%m') AS CREATEDATE,COUNT(1) AS NUM FROM ARTICAL GROUP BY date_format(CREATEDATE, '%Y-%m') ORDER BY CREATEDATE DESC")
    List<Map> listArchive();

    @Select("SELECT * FROM ARTICAL WHERE CREATEDATE LIKE CONCAT(#{dateStr},'%') AND SHOW_FLAG='Y' ORDER BY CREATEDATE DESC")
    List<Artical> findByCreateDateYm(String dateStr);

    @Select("SELECT * FROM ARTICAL WHERE ID=#{pk}")
    Artical find(int pk);

    @Select("SELECT * FROM ARTICAL WHERE TITLE LIKE CONCAT('%',#{keyword},'%') OR CONTENT LIKE CONCAT('%',#{keyword},'%') AND SHOW_FLAG='Y'")
    List<Artical> findByKeyword(String keyword);

    @Select("select t1.id,t2.nickname as author,t1.title as title,case when t1.show_flag='Y' then '开放浏览' else '私密浏览' end as showflag,(select category_name from category where id=t1.categoryid) as type,case when t1.intop='on' then 'checked' else '' end as intop,t1.createdate from artical t1 left join user t2 on t1.author=t2.username  order by t1.id desc")
    List<Map> listMgr();

    @Select("select count(1) from artical")
    int countMgr();

    @Update("update artical set intop=#{flag} where id=#{id}")
    void updateIntopSts(int id, String flag);

    @Update("UPDATE ARTICAL SET " +
            "TITLE=#{title}," +
            "ORIGINAUTHOR=#{originauthor}," +
            "ORIGINURL=#{originurl}," +
            "NAVID=#{navid}," +
            "TOPICID=#{topicid}," +
            "CATEGORYID=#{categoryid}," +
            "TYPE=#{type}," +
            "UPDATEDATE=#{updatedate}," +
            "SUMMARY=#{summary}," +
            "INTOP=#{intop}," +
            "SHOW_FLAG=#{showFlag} WHERE ID=#{id}")
    int update(Artical artical);

    @Update("UPDATE ARTICAL SET CONTENT=#{content} WHERE ID=#{id}")
    void updateContent(Artical artical);

    @Select("SELECT * FROM ARTICAL WHERE TAGS LIKE CONCAT('%',#{tagName},'%')")
    List<Artical> findByTags(String tagName);

    @Select("SELECT * FROM ARTICAL WHERE ID<#{id} ORDER BY ID  DESC LIMIT 1 ")
    Artical findLast(int id);

    @Select("SELECT * FROM ARTICAL WHERE ID>#{id} ORDER BY ID ASC LIMIT 1")
    Artical findNext(int id);

    @Select("SELECT * FROM ARTICAL WHERE TOPICID=#{topicId} ORDER BY ID ASC")
    List<Artical> findByTopicId(Integer topicId);
}
